package com.chenjinyan.service.impl;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.chenjinyan.dao.GoodsRepository;
import com.chenjinyan.dao.MessageRepository;
import com.chenjinyan.dao.PicUrlRepository;
import com.chenjinyan.dao.UserRepository;
import com.chenjinyan.entity.*;
import com.chenjinyan.service.GoodsService;
import com.chenjinyan.utils.SimilarityRatioUtils;
import com.chenjinyan.utils.StringUtils;
import com.chenjinyan.vo.AnswerVo;
import com.chenjinyan.vo.GoodsInfoVo;
import com.chenjinyan.vo.MessageResp;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import javax.transaction.Transactional;
import java.io.File;
import java.math.BigInteger;
import java.util.*;

@Service
@Transactional
public class GoodsServiceImpl implements GoodsService {

    @Autowired
    GoodsRepository goodsRepository;
    @Autowired
    UserRepository userRepository;
    @Autowired
    PicUrlRepository picUrlRepository;
    @Autowired
    MessageRepository messageRepository;

    @PersistenceContext
    private EntityManager em;

    @Override
    public Long saveGoodsInfo(GoodsEntity goodsEntity) {
        GoodsEntity goodsEntityFromSql = goodsRepository.saveAndFlush(goodsEntity);
        return goodsEntityFromSql.getId();
    }

    @Override
    public UserEntity getUserIdByUsername(String userName) {
        List<UserEntity> list = userRepository.findByLoginAccount(userName);
        if (null == list || list.size() != 1) {
            return null;
        }
        return list.get(0);
    }

    @Override
    public List<GoodsEntity> findById(Long id) {
        List<GoodsEntity> list = goodsRepository.findById(id);
        return list;
    }

    @Override
    public void savePicUrl(PicUrlEntity picUrlEntity) {
        picUrlRepository.saveAndFlush(picUrlEntity);
    }

    @Override
    public JSONObject getGoodsInfo(GoodsInfoVo goodsInfoVo) {
        Map<String, String> map = new HashMap<>(16);
        Long userIdL = goodsInfoVo.getUserId();
        String pageNo = goodsInfoVo.getPageNo();
        String pageSize = goodsInfoVo.getPageSize();
        Integer typeOfGoods = goodsInfoVo.getTypeOfGoods();
        Long fromTime = goodsInfoVo.getFromTime();
        Long toTime = goodsInfoVo.getToTime();
        Integer province = goodsInfoVo.getProvince();
        Integer city = goodsInfoVo.getCity();
        Integer district = goodsInfoVo.getDistrict();
        Long id = goodsInfoVo.getId();
        String infoTittle = goodsInfoVo.getInfoTittle();
        Integer goodsWay = goodsInfoVo.getGoodsWay();
        Integer goodsStatus = goodsInfoVo.getStatus();
        Date fromDate = null;
        if (null != fromTime) {
            fromDate = new Date(fromTime);
        }

        Date toDate = null;
        if (null != toTime) {
            toDate = new Date(toTime);
        }
        Integer thankWay = goodsInfoVo.getThankWay();
        String userId;
        if (null == userIdL) {
            userId = "";
        } else {
            userId = String.valueOf(userIdL);
        }
        if (null == pageNo || "".equals(pageNo)) {
            pageNo = "1";
        }
        if (null == pageSize || "".equals(pageSize)) {
            pageSize = "50";
        }

        StringBuilder sql = builderSql();
        StringBuilder sqlCount = builderSqlCount();
        if (null != userId && !"".equals(userId)) {
            //用户id
            sql.append(" AND b.USER_ID = :userId ");
            sqlCount.append(" AND b.USER_ID = :userId ");
        }
        if (null != typeOfGoods) {
            //失物种类
            sql.append(" AND b.TYPE_OF_GOODS = :typeOfGoods ");
            sqlCount.append(" AND b.TYPE_OF_GOODS = :typeOfGoods ");
        }
        if (null != fromTime) {
            //开始时间
            sql.append(" AND b.LOST_TIME >= :fromTime ");
            sqlCount.append(" AND b.LOST_TIME >= :fromTime ");
        }
        if (null != toTime) {
            //结束时间
            sql.append(" AND b.LOST_TIME <= :toTime ");
            sqlCount.append(" AND b.LOST_TIME <= :toTime ");
        }
        if (null != thankWay) {
            //感谢方式
            sql.append(" AND b.THANK_WAY = :thankWay ");
            sqlCount.append(" AND b.THANK_WAY = :thankWay ");
        }
        if (null != id) {
            //失物id
            sql.append(" AND b.ID = :id ");
            sqlCount.append(" AND b.ID = :id ");
        }
        if (null != province) {
            //省
            sql.append(" AND b.PROVINCE = :province ");
            sqlCount.append(" AND b.PROVINCE = :province ");
        }
        if (null != city) {
            //市
            sql.append(" AND b.CITY = :city ");
            sqlCount.append(" AND b.CITY = :city ");
        }
        if (null != district) {
            //区
            sql.append(" AND b.DISTRICT = :district ");
            sqlCount.append(" AND b.DISTRICT = :district ");
        }
        if (null != infoTittle) {
            //区
            sql.append(" AND b.INFO_TITTLE like CONCAT('%',:infoTittle,'%') ");
            sqlCount.append(" AND b.INFO_TITTLE like CONCAT('%',:infoTittle,'%') ");
        }
        if (null != goodsWay) {
            //区
            sql.append(" AND b.GOODS_WAY = :goodsWay ");
            sqlCount.append(" AND b.GOODS_WAY = :goodsWay ");
        }
        if (null != goodsStatus) {
            //区
            sql.append(" AND b.GOODS_STATUS = :goodsStatus ");
            sqlCount.append(" AND b.GOODS_STATUS = :goodsStatus ");
        }

        sql.append(" GROUP BY b.ID ");
        sqlCount.append(" GROUP BY b.ID ");
        sql.append(" ORDER BY b.LOST_TIME DESC");
        sqlCount.append(" ORDER BY b.LOST_TIME DESC");
        //StringBuilder sqlCount = builderSqlCount(userId);
        Query q = em.createNativeQuery(sql.toString(), GoodsEntityForQuery.class);
        Query countQ = em.createNativeQuery(sqlCount.toString(), GoodsEntityForQuery.class);
        //Query countQuery = em.createNativeQuery(sqlCount.toString(), GoodsEntity.class);
        if (!StringUtils.isEmpty(userId) && !StringUtils.isEmpty(userId)) {
            q.setParameter("userId", userId);
            countQ.setParameter("userId", userId);

        }
        if (null != typeOfGoods) {
            q.setParameter("typeOfGoods", typeOfGoods);
            countQ.setParameter("typeOfGoods", typeOfGoods);
        }
        if (null != fromTime) {
            q.setParameter("fromTime", fromDate);
            countQ.setParameter("fromTime", fromDate);
        }
        if (null != toTime) {
            q.setParameter("toTime", toDate);
            countQ.setParameter("toTime", toDate);
        }
        if (null != thankWay) {
            q.setParameter("thankWay", thankWay);
            countQ.setParameter("thankWay", thankWay);
        }
        if (null != id) {
            q.setParameter("id", id);
            countQ.setParameter("id", id);
        }
        if (null != province) {
            q.setParameter("province", province);
            countQ.setParameter("province", province);
        }
        if (null != city) {
            q.setParameter("city", city);
            countQ.setParameter("city", city);
        }
        if (null != district) {
            q.setParameter("district", district);
            countQ.setParameter("district", district);
        }
        if (null != infoTittle) {
            q.setParameter("infoTittle", infoTittle);
            countQ.setParameter("infoTittle", infoTittle);
        }
        if (null != goodsWay) {
            q.setParameter("goodsWay", goodsWay);
            countQ.setParameter("goodsWay", goodsWay);
        }
        if (null != goodsStatus) {
            q.setParameter("goodsStatus", goodsStatus);
            countQ.setParameter("goodsStatus", goodsStatus);
        }

        q.setFirstResult((Integer.parseInt(pageNo) - 1) * Integer.parseInt(pageSize));
        q.setMaxResults(Integer.parseInt(pageSize));
        List<GoodsEntityForQuery> list = q.getResultList();
        JSONArray jsonArray = new JSONArray();
        for (GoodsEntityForQuery goodsEntity : list) {
            Long userIds = goodsEntity.getUserId();
            long goodsId = goodsEntity.getId();
            StringBuilder sqlName = builderSqlForUserName();
            StringBuilder sqlMessage = builderSqlForMessage();
            Query queryName = em.createNativeQuery(sqlName.toString(), UserEntity.class);
            Query queryMessage = em.createNativeQuery(sqlMessage.toString());
            queryName.setParameter("id", userIds);
            queryMessage.setParameter("id", goodsId);
            List<UserEntity> listName = queryName.getResultList();
            List<Object[]> listMessage = queryMessage.getResultList();
            if (null != listName && listName.size() == 1) {
                if (null == listName.get(0).getName()) {
                    goodsEntity.setName("");
                } else {
                    goodsEntity.setName(listName.get(0).getName());
                }
                if (null == listName.get(0).getNetName()) {
                    goodsEntity.setNetName("");
                } else {
                    goodsEntity.setNetName(listName.get(0).getName());
                }
                if (null == listName.get(0).getLoginAccount()) {
                    goodsEntity.setLoginAccount("");
                } else {
                    goodsEntity.setName(listName.get(0).getName());
                }
                goodsEntity.setNetName(listName.get(0).getNetName());
                goodsEntity.setNetName(listName.get(0).getNetName());
            } else {
                goodsEntity.setName("");
                goodsEntity.setNetName("");
                goodsEntity.setLoginAccount("");
            }
            if (null != listMessage && listMessage.size() > 0) {
                List<MessageEntity> stringList = new ArrayList<>();
                for (Object[] objects : listMessage) {
                    MessageEntity messageEntity = new MessageEntity();
                    BigInteger goodId = (BigInteger) objects[0];
                    Long goodsIdL = goodId.longValue();
                    messageEntity.setGoodsId(goodsIdL);
                    messageEntity.setMessage((String) objects[1]);
                    messageEntity.setLeaveTime((Date) objects[2]);
                    messageEntity.setName((String) objects[3]);
                    stringList.add(messageEntity);
                }
                goodsEntity.setMessage(stringList);
            } else {
                goodsEntity.setMessage(new ArrayList<>());
            }
            Integer provinceId = goodsEntity.getProvince();
            Integer cityId = goodsEntity.getCity();
            Integer districtId = goodsEntity.getDistrict();
            if (null != provinceId && null != cityId && null != districtId) {
                StringBuilder sqlArea = builderSqlForAreaName();
                Query queryArea = em.createNativeQuery(sqlArea.toString(), RegionEntity.class);
                queryArea.setParameter("provinceId", provinceId);
                queryArea.setParameter("cityId", cityId);
                queryArea.setParameter("districtId", districtId);
                List<RegionEntity> listArea = queryArea.getResultList();
                if (null != listArea && listArea.size() == 1) {
                    goodsEntity.setProvinceName(listArea.get(0).getProvinceName());
                    goodsEntity.setCityName(listArea.get(0).getCityName());
                    goodsEntity.setDistrictName(listArea.get(0).getDistrictName());
                }
            }
            jsonArray.add(goodsEntity);
        }
        Integer recordCount = countQ.getResultList().size();
        //Integer recordCount = q.getResultList().size();
        Long pageCount = getPageCount(Integer.parseInt(pageSize), recordCount.longValue());

        map.put("currentPage", pageNo);
        map.put("pageSize", pageSize);
        map.put("recordCount", String.valueOf(recordCount));
        map.put("pageCount", String.valueOf(pageCount));
        map.put("goods", jsonArray.toString());
        return JSONObject.parseObject(JSON.toJSONString(map));
    }

    @Override
    public JSONObject delGoodsInfo(String path, List<String> goodsIds) {
        StringBuilder sqlForPic = builderGetPicUrl(goodsIds);
        Query qPic = em.createNativeQuery(sqlForPic.toString(), PicUrlEntity.class);
        qPic.setParameter("goods", goodsIds);

        StringBuilder sqlForMessage = builderGetMessage(goodsIds);
        Query qMessage = em.createNativeQuery(sqlForMessage.toString(), MessageEntity.class);
        qMessage.setParameter("goods", goodsIds);
        List<MessageEntity> listOfMessage = qMessage.getResultList();
        if (null != listOfMessage || listOfMessage.size() > 0) {
            StringBuilder sql = builderDelMessage(goodsIds);
            Query q = em.createNativeQuery(sql.toString());
            q.setParameter("goods", goodsIds);
            q.executeUpdate();
        }
        List<PicUrlEntity> list = qPic.getResultList();
        Map<String, String> map = new HashMap<>(16);
        if (null == list || list.size() == 0) {
            map.put("info", "传入id未查询到图片信息");
            JSONObject itemJSONObj = JSONObject.parseObject(JSON.toJSONString(map));
            return itemJSONObj;
        }
        for (PicUrlEntity picUrlEntity : list) {
            String picUrl = picUrlEntity.getGoodsPicUrl();
            String picName = picUrl.substring(picUrl.lastIndexOf("/") + 1);
            String fileName = path + picName;
            File file = new File(fileName);
            if (file.exists()) {
                file.delete();
            }
        }
        StringBuilder sql = builderDelPicUrl(goodsIds);
        Query q = em.createNativeQuery(sql.toString());
        q.setParameter("goods", goodsIds);
        q.executeUpdate();
        StringBuilder sqlGoods = builderDelSqlGoods(goodsIds);
        Query qGoods = em.createNativeQuery(sqlGoods.toString());
        qGoods.setParameter("goods", goodsIds);
        qGoods.executeUpdate();
        map.put("info", "删除成功");
        return JSONObject.parseObject(JSON.toJSONString(map));
    }

    @Override
    public JSONObject getMessages(String goodsId, String pageNo, String pageSize) {
        if (null == pageNo || "".equals(pageNo)) {
            pageNo = "1";
        }
        if (null == pageSize || "".equals(pageSize)) {
            pageSize = "50";
        }
        StringBuilder sql = builderMessageSql();
        StringBuilder sqlCount = builderMessageSqlCount();
        Query q = em.createNativeQuery(sql.toString());
        Query countQ = em.createNativeQuery(sqlCount.toString());
        q.setParameter("goodsId", goodsId);
        countQ.setParameter("goodsId", goodsId);
        q.setFirstResult((Integer.parseInt(pageNo) - 1) * Integer.parseInt(pageSize));
        q.setMaxResults(Integer.parseInt(pageSize));
        List<Object[]> list = q.getResultList();
        List<MessageResp> messageResps = new ArrayList<>();
        for (Object[] objects : list) {
            MessageResp messageResp = new MessageResp();
            Long id = ((BigInteger) objects[0]).longValue();
            Long goodsIds = ((BigInteger) objects[1]).longValue();
            String message = (String) objects[2];
            Date leaveTime = (Date) objects[3];
            Long userId = ((BigInteger) objects[4]).longValue();
            String name = (String) objects[5];
            if (null == name) {
                name = "";
            }
            String loginAccount = (String) objects[6];
            if (null == loginAccount) {
                loginAccount = "";
            }
            messageResp.setId(id);
            messageResp.setGoodsId(goodsIds);
            messageResp.setMessage(message);
            messageResp.setLeaveTime(leaveTime);
            messageResp.setUserId(userId);
            messageResp.setName(name);
            messageResp.setLoginAccount(loginAccount);
            messageResps.add(messageResp);
        }
        JSONArray jsonArray = new JSONArray();
        jsonArray.addAll(messageResps);
        Integer recordCount = countQ.getResultList().size();
        Long pageCount = getPageCount(Integer.parseInt(pageSize), recordCount.longValue());
        Map<String, String> map = new HashMap<>(16);
        map.put("currentPage", pageNo);
        map.put("pageSize", pageSize);
        map.put("recordCount", String.valueOf(recordCount));
        map.put("pageCount", String.valueOf(pageCount));
        map.put("goods", jsonArray.toString());
        return JSONObject.parseObject(JSON.toJSONString(map));
    }

    @Override
    public String findAllProvince() {
        StringBuilder sql = builderSqlForProvince();
        Query q = em.createNativeQuery(sql.toString());
        List<Object[]> list = q.getResultList();
        return JSONObject.toJSONString(list);
    }

    @Override
    public String findCityByProvince(int provinceId) {
        StringBuilder sql = builderSqlForCity();
        Query q = em.createNativeQuery(sql.toString());
        q.setParameter("province_id", provinceId);
        List<Object[]> list = q.getResultList();
        return JSONObject.toJSONString(list);
    }

    @Override
    public String findAreaByCity(int cityId) {
        StringBuilder sql = builderSqlForArea();
        Query q = em.createNativeQuery(sql.toString());
        q.setParameter("city_id", cityId);
        List<Object[]> list = q.getResultList();
        return JSONObject.toJSONString(list);
    }

    @Override
    public JSONObject answerQuestion(AnswerVo answerVo) {
        Map<String, String> map = new HashMap<>(16);
        Long id = answerVo.getGoodId();
        if (null == id) {
            map.put("info", "请传入失物或招领物品id");
            return JSONObject.parseObject(JSON.toJSONString(map));
        }
        String answer1 = answerVo.getFindGoodsAnswer1();
        String answer2 = answerVo.getFindGoodsAnswer2();
        String answer3 = answerVo.getFindGoodsAnswer3();
        float i1 = 0;
        float i2 = 0;
        float i3 = 0;

        StringBuilder sql = builderSqlForQuestion();
        Query qPic = em.createNativeQuery(sql.toString(), GoodsEntityForAnswer.class);
        qPic.setParameter("id", id);
        List<GoodsEntityForAnswer> list = qPic.getResultList();
        if (null != list && list.size() > 0) {
            String answer1FromSql = list.get(0).getFindGoodsAnswer1();
            String answer2FromSql = list.get(0).getFindGoodsAnswer2();
            String answer3FromSql = list.get(0).getFindGoodsAnswer3();
            if (null != answer1 && !"".equals(answer1) && null != answer1FromSql && !"".equals(answer1FromSql)) {
                i1 = SimilarityRatioUtils.getSimilarityRatio(answer1, answer1FromSql);
            }
            if (null != answer2 && !"".equals(answer2) && null != answer2FromSql && !"".equals(answer2FromSql)) {
                i2 = SimilarityRatioUtils.getSimilarityRatio(answer2, answer2FromSql);
            }
            if (null != answer3 && !"".equals(answer3) && null != answer3FromSql && !"".equals(answer3FromSql)) {
                i3 = SimilarityRatioUtils.getSimilarityRatio(answer3, answer3FromSql);
            }
            if (i1 > 0.5f || i2 > 0.5f || i3 > 0.5f) {
                //任何答案像是程度大于0.5返回联系信息
                StringBuilder sqlForConc = builderSqlForConc();
                Query query = em.createNativeQuery(sqlForConc.toString(), GoodsEntityForConc.class);
                query.setParameter("id", id);
                List<GoodsEntityForConc> listForConc = query.getResultList();
                if (null != listForConc && listForConc.size() == 1) {
                    GoodsEntityForConc goodsEntityForConc = listForConc.get(0);
                    map.put("info", goodsEntityForConc.toString());
                    return JSONObject.parseObject(JSON.toJSONString(map));
                }
            } else {
                map.put("info", "输入答案有误");
                return JSONObject.parseObject(JSON.toJSONString(map));
            }
        }
        map.put("info", "未找到问题");
        return JSONObject.parseObject(JSON.toJSONString(map));
    }

    @Override
    public JSONObject getQuestion(Long goodId) {
        Map<String, String> map = new HashMap<>(16);
        StringBuilder sql = builderSqlForGetQuestion();
        Query query = em.createNativeQuery(sql.toString(), GoodsEntityForQuestion.class);
        query.setParameter("id", goodId);
        List<GoodsEntityForQuestion> list = query.getResultList();
        if (null != list && list.size() == 1) {
            GoodsEntityForQuestion goodsEntityForQuestion = list.get(0);
            map.put("info", goodsEntityForQuestion.toString());
            return JSONObject.parseObject(JSON.toJSONString(map));
        }
        map.put("info", "未设置问题");
        return JSONObject.parseObject(JSON.toJSONString(map));
    }

    @Override
    public void leaveMessage(MessageEntity messageEntity) {
        messageRepository.saveAndFlush(messageEntity);
    }

    private StringBuilder builderSql() {
        StringBuilder sql = new StringBuilder(500);
        sql.append("SELECT ")
                .append("b.ID,b.USER_ID,b.TYPE_OF_GOODS,b.INFO_TITTLE,b.DESCRIPTION,b.LOST_PLACE,b.LOST_TIME,b.CREATE_TIME,b.PHONE_NO, ")
                //.append("b.CONC_PLACE,b.CONC_PERSION,b.PHONE_NO,b.EMAIL,b.TELPHONE_NO,b.MSN,b.QQ,b.THANK_WAY, ")
                .append("b.CONC_PLACE,b.CONC_PERSION,b.THANK_WAY, ")
                .append("b.THANK_MONEY_AMOUNT,b.GOODS_WAY,b.FIND_GOODS_QUESTION_1, ")
                .append("b.FIND_GOODS_QUESTION_2,b.FIND_GOODS_QUESTION_3, ")
                //.append("b.FIND_GOODS_ANSWER_1,b.FIND_GOODS_ANSWER_2,b.FIND_GOODS_ANSWER_3, ")
                .append("b.CITY,b.PROVINCE,b.DISTRICT, ")
                .append("a.ID,a.GOODS_PIC_URL,a.GOODS_PIC_URL,b.GOODS_STATUS ")
                //.append("c.PROVINCE_NAME,c.CITY_NAME,c.DISTRICT_NAME ")
                .append("FROM lost_and_find_goods b ")
                .append("LEFT JOIN lost_and_find_pic_url a ON a.GOODS_ID=b.ID ")
                .append("WHERE 1=1 ");
        //.append("LEFT JOIN lost_and_find_region c ON b.PROVINCE=c.PROVINCE_ID AND b.CITY=c.CITY_ID AND b.DISTRICT = c.DISTRICT_ID ");
        return sql;
    }

    private StringBuilder builderSqlCount() {
        StringBuilder sql = new StringBuilder(500);
        sql.append("SELECT ")
                .append("b.ID,b.USER_ID,b.TYPE_OF_GOODS,b.INFO_TITTLE,b.DESCRIPTION,b.LOST_PLACE,b.LOST_TIME,b.CREATE_TIME,b.PHONE_NO, ")
                //.append("b.CONC_PLACE,b.CONC_PERSION,b.PHONE_NO,b.EMAIL,b.TELPHONE_NO,b.MSN,b.QQ,b.THANK_WAY, ")
                .append("b.CONC_PLACE,b.CONC_PERSION,b.THANK_WAY, ")
                .append("b.THANK_MONEY_AMOUNT,b.GOODS_WAY,b.FIND_GOODS_QUESTION_1, ")
                .append("b.FIND_GOODS_QUESTION_2,b.FIND_GOODS_QUESTION_3, ")
                //.append("b.FIND_GOODS_ANSWER_1,b.FIND_GOODS_ANSWER_2,b.FIND_GOODS_ANSWER_3, ")
                .append("b.CITY,b.PROVINCE,b.DISTRICT, ")
                .append("a.ID,a.GOODS_PIC_URL,a.GOODS_PIC_URL,b.GOODS_STATUS  ")
                //.append("c.PROVINCE_NAME,c.CITY_NAME,c.DISTRICT_NAME ")
                .append("FROM lost_and_find_goods b ")
                .append("LEFT JOIN lost_and_find_pic_url a ON a.GOODS_ID=b.ID ")
                .append("WHERE 1=1 ");
        //.append("LEFT JOIN lost_and_find_region c ON b.PROVINCE=c.PROVINCE_ID AND b.CITY=c.CITY_ID AND b.DISTRICT = c.DISTRICT_ID ");
        return sql;
    }


    private StringBuilder builderMessageSql() {
        StringBuilder sql = new StringBuilder(500);
        sql.append("SELECT ")
                .append("a.ID,a.GOOD_ID,a.MESSAGE,a.LEAVE_TIME,a.USER_ID,c.NET_NAME,c.LOGIN_ACCOUNT ")
                .append("FROM lost_and_find_message a ")
                .append("left join lost_and_find_goods b ON a.GOOD_ID=b.ID ")
                .append("left join lost_and_find_user c ON a.USER_ID=c.ID ")
                .append("WHERE a.GOOD_ID=:goodsId ");
        return sql;
    }

    private StringBuilder builderMessageSqlCount() {
        StringBuilder sql = new StringBuilder(500);
        sql.append("SELECT ")
                .append("a.ID,a.GOOD_ID,a.MESSAGE,a.LEAVE_TIME,a.USER_ID,c.NET_NAME,c.LOGIN_ACCOUNT ")
                .append("FROM lost_and_find_message a ")
                .append("left join lost_and_find_goods b ON a.GOOD_ID=b.ID ")
                .append("left join lost_and_find_user c ON a.USER_ID=c.ID ")
                .append("WHERE a.GOOD_ID=:goodsId ");
        return sql;
    }


    private StringBuilder builderSqlForConc() {
        StringBuilder sql = new StringBuilder(500);
        sql.append("SELECT ")
                .append("ID,USER_ID,TYPE_OF_GOODS,INFO_TITTLE,DESCRIPTION,LOST_PLACE,LOST_TIME, ")
                .append("CONC_PLACE,CONC_PERSION,PHONE_NO,EMAIL,TELPHONE_NO,MSN,QQ,THANK_WAY ")
                .append("FROM lost_and_find_goods ")
                .append("WHERE ID=:id ");
        //.append("LEFT JOIN lost_and_find_region c ON b.PROVINCE=c.PROVINCE_ID AND b.CITY=c.CITY_ID AND b.DISTRICT = c.DISTRICT_ID ");
        return sql;
    }

    private StringBuilder builderDelSqlGoods(List<String> goods) {
        StringBuilder sql = new StringBuilder(500);
        sql.append("DELETE FROM ")
                .append("lost_and_find_goods ")
                .append("WHERE ID IN :goods");
        return sql;
    }

    private StringBuilder builderDelPicUrl(List<String> goods) {
        StringBuilder sql = new StringBuilder(500);
        sql.append("DELETE FROM ")
                .append("lost_and_find_pic_url ")
                .append("WHERE GOODS_ID IN :goods");
        return sql;
    }

    private StringBuilder builderDelMessage(List<String> goods) {
        StringBuilder sql = new StringBuilder(500);
        sql.append("DELETE FROM ")
                .append("lost_and_find_message ")
                .append("WHERE GOOD_ID IN :goods");
        return sql;
    }

    private StringBuilder builderGetPicUrl(List<String> goods) {
        StringBuilder sql = new StringBuilder(500);
        sql.append("SELECT ID,GOODS_ID,GOODS_PIC_URL FROM ")
                .append("lost_and_find_pic_url ")
                .append("WHERE GOODS_ID IN :goods");
        return sql;
    }

    private StringBuilder builderGetMessage(List<String> goods) {
        StringBuilder sql = new StringBuilder(500);
        sql.append("SELECT ID,GOOD_ID,MESSAGE,LEAVE_TIME,USER_ID FROM ")
                .append("lost_and_find_message ")
                .append("WHERE GOOD_ID IN :goods");
        return sql;
    }

    private StringBuilder builderSqlForProvince() {
        StringBuilder sql = new StringBuilder(500);
        sql.append("SELECT province_id,province_name from lost_and_find_region GROUP BY province_id ");
        return sql;
    }

    private StringBuilder builderSqlForCity() {
        StringBuilder sql = new StringBuilder(500);
        sql.append("SELECT city_id,city_name from lost_and_find_region WHERE province_id =:province_id GROUP BY city_id ");
        return sql;
    }

    private StringBuilder builderSqlForArea() {
        StringBuilder sql = new StringBuilder(500);
        sql.append("SELECT district_id,district_name from lost_and_find_region WHERE city_id =:city_id ");
        return sql;
    }

    private StringBuilder builderSqlForAreaName() {
        StringBuilder sql = new StringBuilder(500);
        sql.append("SELECT ID,PROVINCE_ID,PROVINCE_NAME,CITY_ID,CITY_NAME,DISTRICT_ID,DISTRICT_NAME from lost_and_find_region WHERE " +
                "PROVINCE_ID =:provinceId and CITY_ID =:cityId and DISTRICT_ID =:districtId ");
        return sql;
    }

    private StringBuilder builderSqlForUserName() {
        StringBuilder sql = new StringBuilder(500);
        sql.append("SELECT * from lost_and_find_user WHERE " +
                "ID =:id  ");
        return sql;
    }

    private StringBuilder builderSqlForMessage() {
        StringBuilder sql = new StringBuilder(500);
        sql.append("SELECT a.GOOD_ID,a.MESSAGE,a.LEAVE_TIME,b.NET_NAME from lost_and_find_message a left join lost_and_find_user b on a.USER_ID=b.id WHERE " +
                "GOOD_ID =:id  ");
        return sql;
    }

    private Long getPageCount(Integer pageSize, Long recordCount) {
        return recordCount % pageSize == 0 ? recordCount / pageSize : recordCount / pageSize + 1;
    }

    private StringBuilder builderSqlForQuestion() {
        StringBuilder sql = new StringBuilder(500);
        sql.append("SELECT ID,FIND_GOODS_ANSWER_1,FIND_GOODS_ANSWER_2,FIND_GOODS_ANSWER_3 from lost_and_find_goods WHERE " +
                "ID =:id ");
        return sql;
    }

    private StringBuilder builderSqlForGetQuestion() {
        StringBuilder sql = new StringBuilder(500);
        sql.append("SELECT ID,FIND_GOODS_QUESTION_1,FIND_GOODS_QUESTION_2,FIND_GOODS_QUESTION_3 from lost_and_find_goods WHERE " +
                "ID =:id ");
        return sql;
    }

}
